#
#Copyright (c) 2013 Luciano Xumerle. All rights reserved.
#
# This file is part of gephinfo.
#
# gephinfo is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.
#

package myPg;

require 5.006_000;
use warnings;
use strict;
use DBI;

@myPg::ISA = qw ( DBI::db );

=head1 NAME

myPg

=head1 SYNOPSIS

use myPg;
my $dbh = myPg->connect($database, $user, $password,$host);


=head1 DESCRIPTION

The class provides methods for managing a PostgreSQL database.

=head1 FUNZIONI DI LOGIN DATABASE E CGI

=over 3

=item B<connect($database, $user, $password, $host)>
=item B<connect($file)>

The method returns the database connection.

File example:

DATABASE	music
DBUSER		luciano
DBPASS		mypassword
DBIP		192.168.1.89

=cut

sub connect
{
    my $proto    = shift;
    my $database = shift;
    my $user     = shift;
    my $pass     = shift;
    my $host     = shift;
    my $class    = ref($proto) || $proto;

    if ( -f $database )
    {
        my $list = &myPg::_fileParse($database);
        if ( defined $list )
        {
            ( $database, $user, $pass, $host ) = @$list;
        }
        else { return undef; }
    }

    my $self = DBI->connect( "dbi:Pg:dbname=$database;host=$host;port=5432",
        $user, $pass );
    return undef if ( !defined $self );
    bless( $self, $class );
    return $self;
}

sub _fileParse
{
    my $file = shift;

    my $dbpass = undef;
    my $dbuser = undef;
    my $dbip   = undef;
    my $db     = undef;

    open( FILE, $file )
      || die "You must create ~/$file file!!!\n\n";
    while (<FILE>)
    {
        chomp;
        if (m/^\s*DATABASE\s+(.+)\s*$/)
        {
            $db = $1;
        }
        elsif (m/^\s*DBUSER\s*(.+)\s*$/)
        {
            $dbuser = $1;
        }
        elsif (m/^\s*DBPASS\s*(.+)\s*$/)
        {
            $dbpass = $1;
        }
        elsif (m/^\s*DBIP\s*(.+)\s*$/)
        {
            $dbip = $1;
        }
    }
    close FILE;

    if ( defined $db && defined $dbuser && defined $dbpass && defined $dbip )
    {
        return [ $db, $dbuser, $dbpass, $dbip ];
    }
    return undef;
}

=item B<endDB($dbh)>

Closes database connection.

=cut

sub endDB { my $dbh = shift; $dbh->disconnect if ( defined $dbh ); }

=item B<doSQL($dbh, $sql)>

The method computes a SQL statement.

=cut

sub doSQL
{
    my $dbh = shift;
    my $sql = shift;
    $dbh
      || print STDERR "\n--- ERROR DBH ---\n" . "No database connection.\n";
    $dbh->do($sql)
      || print STDERR "--- ERROR doSQL ---\n"
      . "Incorrect SQL.\n"
      . $sql
      . qq|\n$dbh::err : $dbh::errstr|;
}

=item B<executeSQL($dbh, $sql)>

The method computes a SQL query. Returns $sth.

=cut

sub executeSQL
{
    my $dbh = shift;
    my $sql = shift;
    $dbh
      || print STDERR "\n--- ERROR DBH ---\n" . "No database connection.\n";
    my $sth = $dbh->prepare($sql)
      || print STDERR "--- ERROR executeSQL ---\n"
      . "Incorrect SQL.\n"
      . $sql
      . qq|\n$dbh::err : $dbh::errstr|;
    $sth->execute()
      || print STDERR "--- ERROR executeSQL ---\n"
      . "incorrect SQL.\n"
      . $sql
      . qq|\n$dbh::err : $dbh::errstr|;
    return $sth;
}

=item B<getQuoted($value)>

Quotes string, trim string and replace multiple spaces with a single space.

=cut

sub getQuoted
{
    my $dbh = shift;
    my $tt  = shift;
    $tt =~ s/^[\s\n\r]+//;
    $tt =~ s/[\s\n\r]+$//;
    $tt =~ s/[\s\n\r]+/ /g;
    $tt = $dbh->quote($tt);
    return $tt;
}

=item B<getAttList($dbh, $table)>

Returns the pointer to the list of attributes in table.

=cut

sub getAttList
{
    my $dbh = shift;
    my $sql = shift;
    my $sth = $dbh->prepare("SELECT * FROM $sql WHERE 1=0");
    $sth->execute;
    return $sth->{NAME};
}

sub getAttList_
{
    my $dbh   = shift;
    my $table = shift;

    my $sth = $dbh->prepare("SELECT * FROM $table WHERE 1=0");
    $sth->execute;

    my $atts = $sth->{NAME};
    my $type = $sth->{pg_type};
    my $prec = $sth->{PRECISION};

    my %atts = ();
    $atts{'=ORDER='} = [];
    for ( my $i = 0 ; $i <= $#$atts ; $i++ )
    {
        if ( $type->[$i] =~ m/varchar/ )
        {
            $type->[$i] = $type->[$i] . '(' . ( $prec->[$i] - 4 ) . ')';
        }
        $atts{ $atts->[$i] } = $type->[$i];
        push @{ $atts{'=ORDER='} }, $atts->[$i];
    }
    return \%atts;
}

=item B<isPresent($dbh, $table)>

Returns 1 if table exist. Default is 0.

=cut

sub isPresent
{
    my $dbh = shift;
    my $sql = shift;
    $sql = qq|SELECT * FROM $sql|;
    my $sth = &myPg::executeSQL( $dbh, $sql );
    ( $sth->rows > 0 ) ? return 1 : return 0;
}

=item B<isAttPresent($dbh, $table, $attribute)>

Returns 1 if attribute in present in table. Defaulti is 0.

=cut

sub isAttPresent
{
    my $dbh = shift;
    my $sql = shift;
    my $att = shift;
    if ( defined $sql && defined $att && $sql =~ m/^\w/i && $att =~ m/^\w/i )
    {
        $sql = "SELECT $att FROM $sql";
        my $sth = &myPg::executeSQL( $dbh, $sql );
        ( $sth->rows > 0 ) ? return 1 : return 0;
    }
    else { return 0; }
}

=item B<getAttType($dbh, $table, $attribute)>

Returns attribute type.

=cut

sub getAttType
{
    my $dbh  = shift;
    my $sql  = shift;
    my $att  = shift;
    my $list = &getAttList_( $dbh, $sql );
    return $list->{$att};
}

=back

=head1 AUTHOR

Luciano Xumerle (luciano.xumerle@gmail.com)
